
import pandas as pd
import numpy as np
from scipy.stats import zscore, gaussian_kde
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import seaborn as sns
import matplotlib.gridspec as gridspec
# import os
import dolphindb as ddb

# 连接dolphin DB
s = ddb.session()# 创建会话
s.connect(host='101.95.132.98',port=33147, userid='CW', password='123456') # 建立连接

t1 =  'select * from skew_data_new  '#从DB 中获取原始数据
data_new = s.run(t1)


# Load the data
# data_new = pd.read_csv('/mnt/data/skew.csv')
# data_new = pd.read_csv("E:\py 文件\偏度数据绘图\skew.csv")
data_new['hour'] = pd.to_datetime(data_new['hour'], format='%Y.%m.%dT%H')

# Calculate Z-score
data_new['z_score'] = data_new.groupby('code')['skew'].transform(lambda x: zscore(x, nan_policy='omit'))

# Filter the data based on Z-score (-6, 6)
data_for_densityplot_new_filtered = data_new[np.abs(data_new['z_score']) < 6]





# Function to plot skewness and density for each code with Z-score filter between -6 and 6
# def plot_skewness_and_density_for_code_with_z_filter(code, data_for_lineplot, data_for_densityplot):
#    Initialize the figure
    # fig = plt.figure(figsize=(15, 5))
    # gs = gridspec.GridSpec(1, 2, width_ratios=[3, 1], wspace=0)
# 
    # Create subplots
    # ax1 = fig.add_subplot(gs[0])
    # ax2 = fig.add_subplot(gs[1])
# 
    # Filter data for the given code
    # data_for_lineplot_code = data_for_lineplot[data_for_lineplot['code'] == code].copy()
    # data_for_densityplot_code = data_for_densityplot[data_for_densityplot['code'] == code].copy()
# 
    # Calculate Z-score for the densityplot data
    # data_for_densityplot_code['z_score'] = zscore(data_for_densityplot_code['skewness'], nan_policy='omit')
# 
    # Filter the data to remove outliers based on Z-score
    # data_for_densityplot_code = data_for_densityplot_code[np.abs(data_for_densityplot_code['z_score']) < 6]
# 
    # Plot the last 5 days' skewness using lineplot
    # lineplot = sns.lineplot(data=data_for_lineplot_code, x='hour', y='skewness', ax=ax1)
    # ax1.set_title(f'Skewness for {code} (Last 5 Days)')
    # ax1.set_xlabel('Hour')
    # ax1.set_ylabel('Skewness')
# 
    # Correct x-axis labels
    # ax1.xaxis.set_major_locator(mdates.HourLocator(interval=4))
    # ax1.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d %H:%M'))
    # ax1.tick_params(axis='x', rotation=45)
# 
    # Compute density estimates for the kdeplot
    # density = gaussian_kde(data_for_densityplot_code['skewness'])
    # x = np.linspace(min(data_for_densityplot_code['skewness']), max(data_for_densityplot_code['skewness']), 500)
    # y = density(x)
# 
    # Get the color of the lineplot
    # line_color = lineplot.get_lines()[0].get_color()
# 
    # Plot the density of skewness on the right subplot
    # ax2.fill_betweenx(x, y, color=line_color, alpha=0.5)
    # ax2.set_title('Density')
    # ax2.set_xlabel('Density')
    # ax2.set_ylabel('')
    # ax2.set_yticks([])
# 
    # plt.tight_layout()
    # plt.show()
# 
# Identify the top 10 codes with most data points
data_count_by_code = data_new.groupby('code').size().reset_index(name='count')
top_8_codes = data_count_by_code.nlargest(8, 'count')['code'].tolist()

# Filter the data for plotting the last 5 days
data_for_lineplot_new = data_new[data_new['hour'] > data_new['hour'].max() - pd.Timedelta(days=5)]


# Plot for each of the top 8 codes with most data points
# for code in top_10_codes[:8]:
    # plot_skewness_and_density_for_code_with_z_filter(code, data_for_lineplot_new, data_for_densityplot_new_filtered)
# 
def plot_all_skewness_and_density_for_codes_adjusted_xaxis(codes, data_for_lineplot, data_for_densityplot):
    n = len(codes)
    # gs = gridspec.GridSpec(n, 2, width_ratios=[2, 2], hspace=0.5,wspace=0)
    gs = gridspec.GridSpec(n, 2, width_ratios=[2,0.5],hspace=0.5, wspace=0)
    fig = plt.figure(figsize=(10, 3*n))
    
    for i, code in enumerate(codes):
        ax1 = fig.add_subplot(gs[i, 0])
        ax2 = fig.add_subplot(gs[i, 1])

        data_for_lineplot_code = data_for_lineplot[data_for_lineplot['code'] == code].copy()
        data_for_densityplot_code = data_for_densityplot[data_for_densityplot['code'] == code].copy()
        data_for_densityplot_code['z_score'] = zscore(data_for_densityplot_code['skew'], nan_policy='omit')
        data_for_densityplot_code = data_for_densityplot_code[np.abs(data_for_densityplot_code['z_score']) < 6]

        # Lineplot
        lineplot = sns.lineplot(data=data_for_lineplot_code, x='hour', y='skewness', ax=ax1)
        
        # Plotting the mean of skewness on the lineplot
        # mean_skewness = data_for_lineplot_code['skewness'].mean()
        # ax1.axhline(mean_skewness, color='red', linestyle='--')
        
        ax1.set_title('')
        ax1.set_xlabel('') #去x轴标题
        # ax1.set_ylabel(f'Skewness for {code} (Last 5 Days)')
        ax1.set_ylabel(f'{code}', weight='bold', size=12)
        ax1.grid(True, linestyle='--', alpha=0.6)
        # Adjust the x-axis date format
        # ax1.xaxis.set_major_locator(mdates.DayLocator()) # 只显示日期
        # ax1.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))
        # ax1.tick_params(axis='x', rotation=45)

        # Show x-axis date labels only for the last subplot
        ax1.xaxis.set_major_locator(mdates.DayLocator())
        if i == len(codes) - 1:
            ax1.xaxis.set_major_locator(mdates.DayLocator())
            ax1.xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m-%d'))
            ax1.tick_params(axis='x', rotation=45)    
        else:
            ax1.set_xticklabels([])


        # Density plot
        density = gaussian_kde(data_for_densityplot_code['skew'])
        x = np.linspace(min(data_for_densityplot_code['skew']), max(data_for_densityplot_code['skew']), 500)
        y = density(x)
        line_color = lineplot.get_lines()[0].get_color()
        ax2.fill_betweenx(x, y, color=line_color, alpha=0.5)
        
        # Plotting the mean of skewness on the density plot
        # ax2.axhline(mean_skewness, color='red', linestyle='--')
        ax2.set_ylim(ax1.get_ylim())
        
        if i == len(codes) - 1:
            ax2.set_xlabel('Density')
        else:
            ax2.set_title('')
            ax2.set_ylabel('')
            ax2.set_yticks([])
                            
    plt.tight_layout()
    plt.suptitle('Skewness')  # Adding a title to the figure

    plt.show()

# Call the function to plot with adjusted x-axis
plot_all_skewness_and_density_for_codes_adjusted_xaxis(top_8_codes, data_for_lineplot_new, data_for_densityplot_new_filtered)
# 
# 













